<?php
class m_public_function extends CI_Model {
	
	function get_progkeg($filter,$start,$end){
		$sql = "SELECT * FROM m_anggaran WHERE anggaran_kd2 = '0' AND anggaran_kd3 = '0' AND 
				anggaran_kd4 = '0' AND anggaran_kd5 = '0' AND 
				anggaran_kd6 = '0' AND anggaran_aktif = 'y'";
		$query = $this->db->query($sql);
		$total = $query->num_rows();
		
		if ($filter<>""){
				$sql .=preg_match("/WHERE/i",$sql)? " AND ":" WHERE ";
				$sql .= " (lower(anggaran_kegiatan) LIKE '%".addslashes(strtolower($filter))."%' )";
		}
		$sql .= " ORDER BY anggaran_kd1";
		$limit = $sql." LIMIT ".$start.", ".$end;
		$query = $this->db->query($limit);
		$arr = array();
		foreach($query->result() as $q){
			$arr[] = $q; 
		}
	
		$final = array(
			'total'   => $total,
			'results' => $arr
		);
		return json_encode($final);
	}
	
		
	function get_prokeg_child($parent,$filter,$start,$end,$data){
		$sql_head = "SELECT *, 
			(anggaran_value  * (
							IF(anggaran_vol1 IS NULL, 1,anggaran_vol1) *
							IF(anggaran_vol2 IS NULL, 1,anggaran_vol2) *
							IF(anggaran_vol3 IS NULL, 1,anggaran_vol3) 
						) - IFNULL(val,0)) as sisa_pagu FROM m_anggaran
			LEFT JOIN 
			v_anggaran_sisapagu ON anggaran_id = m_anggaran_id WHERE anggaran_level = 6 ";
		
		$sql = $sql_head ;
		
		if ($parent<>""){
			$sql .=preg_match("/WHERE/i",$sql)? " AND ":" WHERE ";
			$sql .= " lower(anggaran_kd1) = '".addslashes(strtolower($parent))."' ";
		}
		
		$query = $this->db->query($sql);
		$total = $query->num_rows();
		
		if ($filter<>""){
			$sql .=preg_match("/WHERE/i",$sql)? " AND ":" WHERE ";
			$sql .= " (lower(anggaran_kegiatan) LIKE '%".addslashes(strtolower($filter))."%' )";
		}
		
		if ($total < $start) $start = 0;
		$sql .= " ORDER BY anggaran_kd1";
		$sql = $sql." LIMIT ".$start.", ".$end;
		
		if (count($data)<>0){
			$sql_data = $sql_head;
			$sql_data .=preg_match("/WHERE/i",$sql_data)? " AND ":" WHERE ";
			$sql_data .= "(";
			$i = 0;
			foreach($data as $datum){
				if($i != 0)
					$sql_data .=preg_match("/WHERE/i",$sql_data)? " OR ":" WHERE ";
				$sql_data .= " (lower(anggaran_id) = '".addslashes(strtolower($datum))."' )";
				$i++;
			}
			if ($filter<>""){
				$sql_data .=preg_match("/WHERE/i",$sql_data)? " OR ":" WHERE ";
				$sql_data .= " (lower(anggaran_kegiatan) LIKE '%".addslashes(strtolower($filter))."%' )";
			}
			$sql_data .= ")";
			$sql = "(".$sql.") UNION (".$sql_data.")";
		}
		
		$query = $this->db->query($sql);
		$arr = array();
		foreach($query->result() as $q){
			$arr[] = $q; 
		}
	
		$final = array(
			'total'   => $total,
			'results' => $arr
		);
		return json_encode($final);
	}
	
	function get_akun($parent,$parent_kode,$filter,$start,$end){
		
		$sql = "SELECT * FROM `t_pengajuandet` LEFT JOIN m_anggaran 
		ON m_anggaran_id = anggaran_id 
		";
		
		if ($parent<>""){
				$sql .=preg_match("/WHERE/i",$sql)? " AND ":" WHERE ";
				$sql .= " (lower(t_pengajuan_id) = '".addslashes(strtolower($parent))."' )";
		}
		if ($parent_kode<>""){
				$sql .=preg_match("/WHERE/i",$sql)? " AND ":" WHERE ";
				$sql .= " (lower(anggaran_kd1) = '".addslashes(strtolower($parent_kode))."' )";
		}
		
		if ($filter<>""){
				$sql .=preg_match("/WHERE/i",$sql)? " AND ":" WHERE ";
				$sql .= " (lower(anggaran_kegiatan) LIKE '%".addslashes(strtolower($filter))."%' )";
		}
		
		$query = $this->db->query($sql);
		$total = $query->num_rows();
		
		$limit = $sql." LIMIT ".$start.", ".$end;

		$query = $this->db->query($limit);
		$arr = array();
		foreach($query->result() as $q){
			$arr[] = $q; 
		}
	
		$final = array(
			'total'   => $total,
			'results' => $arr
		);
		return json_encode($final);
	}
	
	function get_no_fpd_pengajuan($filter,$start,$end){
		
		$sql = "SELECT * FROM `t_pengajuan` WHERE pengajuan_status = 'P'";
		
		if ($filter<>""){
				$sql .=preg_match("/WHERE/i",$sql)? " AND ":" WHERE ";
				$sql .= " (lower(pengajuan_no_fpd) LIKE '%".addslashes(strtolower($filter))."%' )";
		}
		
		$query = $this->db->query($sql);
		$total = $query->num_rows();
		
		$limit = $sql." LIMIT ".$start.", ".$end;
		$query = $this->db->query($limit);
		$arr = array();
		foreach($query->result() as $q){
			$arr[] = $q; 
		}
	
		$final = array(
			'total'   => $total,
			'results' => $arr
		);
		return json_encode($final);
	}
	
	function get_no_fpd_pengajuan_akb($filter,$start,$end){
		
		$sql = "SELECT * FROM `t_pengajuan` WHERE pengajuan_status = 'AKP'";
		
		if ($filter<>""){
				$sql .=preg_match("/WHERE/i",$sql)? " AND ":" WHERE ";
				$sql .= " (lower(pengajuan_no_fpd) LIKE '%".addslashes(strtolower($filter))."%' )";
		}
		
		$query = $this->db->query($sql);
		$total = $query->num_rows();
		
		$limit = $sql." LIMIT ".$start.", ".$end;
		$query = $this->db->query($limit);
		$arr = array();
		foreach($query->result() as $q){
			$arr[] = $q; 
		}
	
		$final = array(
			'total'   => $total,
			'results' => $arr
		);
		return json_encode($final);
	}

	function get_progkeg_pengajuan($filter,$start,$end){
		
		$sql = "SELECT * FROM `t_pengajuan` LEFT JOIN m_anggaran 
		ON m_anggaran_id = anggaran_id 
		";
		
		if ($filter<>""){
				$sql .=preg_match("/WHERE/i",$sql)? " AND ":" WHERE ";
				$sql .= " (lower(anggaran_kegiatan) LIKE '%".addslashes(strtolower($filter))."%' )";
		}
		
		$query = $this->db->query($sql);
		$total = $query->num_rows();
		
		$limit = $sql." LIMIT ".$start.", ".$end;
		$query = $this->db->query($limit);
		$arr = array();
		foreach($query->result() as $q){
			$arr[] = $q; 
		}
	
		$final = array(
			'total'   => $total,
			'results' => $arr
		);
		return json_encode($final);
	}
	
	function get_nofpd($filter,$limit,$offset){		
		$this->db->limit($limit);
		$this->db->offset($offset);
		$this->db->select('pengajuan_id, pengajuan_no_fpd,pengajuan_date ,pengajuan_sumberdana ,anggaran_kegiatan ,anggaran_id');
		$this->db->join('m_anggaran','m_anggaran_id = anggaran_id');
		if(!empty($filter)){
			$this->db->where("lower(pengajuan_no_fpd) LIKE '%".addslashes(strtolower($filter))."%'");
		}
		$query = $this->db->get('t_pengajuan');
		$total = $query->num_rows();
		
		$arr = array();
		foreach($query->result() as $q){
			$arr[] = $q; 
		}
	
		$final = array(
			'total'   => $total,
			'results' => $arr
		);
		return json_encode($final);
	}
	
	function get_kegiatan($filter,$limit,$offset){
		
		$this->db->limit($limit);
		$this->db->offset($offset);
		$this->db->select('jeniskegiatan_id, jeniskegiatan_nama');
		if(!empty($filter)){
			$this->db->where("lower(jeniskegiatan_nama) LIKE '%".addslashes(strtolower($filter))."%'");
		}
		$query = $this->db->get('m_jeniskegiatan');
		$total = $this->db->count_all('m_jeniskegiatan');
		
		$arr = array();
		foreach($query->result() as $q){
			$arr[] = $q; 
		}
	
		$final = array(
			'total'   => $total,
			'results' => $arr
		);
		return json_encode($final);
	}
	
	function get_auto_no_fpd($m_anggaran_id) {
		$sql  = "SELECT MAX(pengajuan_id) as jumlah FROM t_pengajuan WHERE m_anggaran_id = $m_anggaran_id";	
		$query = $this->db->query($sql);
		return $query->row('jumlah');
	}
	
	function get_personil($filter,$start,$end){
		$sql = "SELECT * FROM m_personil";
		$query = $this->db->query($sql);
		$total = $query->num_rows();
		
		if ($filter<>""){
				$sql .=preg_match("/WHERE/i",$sql)? " AND ":" WHERE ";
				$sql .= " (lower(personil_nama) LIKE '%".addslashes(strtolower($filter))."%' )";
		}
		$sql .= " ORDER BY personil_id";
		$limit = $sql." LIMIT ".$start.", ".$end;
		$query = $this->db->query($limit);
		$arr = array();
		foreach($query->result() as $q){
			$arr[] = $q; 
		}
	
		$final = array(
			'total'   => $total,
			'results' => $arr
		);
		return json_encode($final);
	}
	function get_year_anggaran(){
		$sql   = "SELECT DISTINCT(anggaran_tahun) FROM m_anggaran WHERE anggaran_tahun IS NOT NULL";
		$query = $this->db->query($sql);
		$arr   = array();
		foreach($query->result() as $q){
			$arr[] = $q;
		}
		$final = array('results' => $arr);
		return json_encode($final);
	}
	// function strip_nol($kode){
		// $kode_arr = explode('.',$kode);
		// for($i=count($kode_arr)-1;$i>0;$i--){
			// if($kode_arr[$i] != '0'){
				// return implode('.',$kode_arr);
				// exit;
			// }else{
				// array_pop($kode_arr);
			// }
		// }
	// }
	
	// function getGridFormat(){
		// $query = $this->db->query($sql);
		// $total = $query->num_rows();
		
		// if ($filter<>""){
				// $sql .=preg_match("/WHERE/i",$sql)? " AND ":" WHERE ";
				// $sql .= " (lower(personil_nama) LIKE '%".addslashes(strtolower($filter))."%' )";
		// }
		// $sql .= " ORDER BY personil_id";
		// $limit = $sql." LIMIT ".$start.", ".$end;
		// $query = $this->db->query($limit);
		// $arr = array();
		// foreach($query->result() as $q){
			// $arr[] = $q; 
		// }
	
		// $final = array(
			// 'total'   => $total,
			// 'results' => $arr
		// );
		// return json_encode($final);
	// }
	function get_satuan($filter,$limit,$offset){
		$this->db->limit($limit);
		$this->db->offset($offset);
		$this->db->select('satuan_id, satuan_nama');
		if(!empty($filter)){
			$this->db->where("lower(satuan_nama) LIKE '%".addslashes(strtolower($filter))."%'");
		}
		$query = $this->db->get('m_satuan');
		$total = $this->db->count_all('m_satuan');
		
		$arr = array();
		foreach($query->result() as $q){
			$arr[] = $q; 
		}
	
		$final = array(
			'total'   => $total,
			'results' => $arr
		);
		return json_encode($final);
	}
}